﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Web;
using CommonLib;
using Entities;
using Entities.System;
using DataAccess.SQL;
using System.Data.SqlClient;

namespace BLL.System
{
    /// <summary>参数逻辑
    /// 
    /// </summary>
    public class ParamBO : Framework.BusinessBase
    {
        /// <summary>取得参数
        /// 
        /// </summary>
        /// <param name="dicLike"></param>
        /// <param name="dicEqual"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNum"></param>
        /// <returns></returns>
        public string getParam(Dictionary<string, string> dicLike, Dictionary<string, string> dicEqual,
            int? pageSize, int? pageNum)
        {
            List<Param> lst = new List<Param>();
            string attachCondition = "";
            if (dicLike.ContainsKey("attachCondition"))
            {
                attachCondition = string.Format("and ({0})", dicLike["attachCondition"]);
                dicLike.Remove("attachCondition");
            }
            var condition = getCondition(dicLike, dicEqual);
            condition += attachCondition;
            string strSql = string.Empty;

            if (pageSize.HasValue && pageNum.HasValue)
            {
                int from = pageSize.Value * (pageNum.Value - 1);
                int to = pageSize.Value * pageNum.Value;
                string strTableName = string.Format(@"(SELECT * FROM SBRM_PARAM where ACTIVE = 'Y' {0} ) U", condition);
                strSql = Common.EasyUIHelper.GetSqlOfPageData(pageNum, pageSize, strTableName, "PARAM_CODE", "", "");
            }
            else
            {
                strSql = string.Format(@"SELECT u.* FROM SBRM_PARAM u where ACTIVE = 'Y' {0}                                                   
ORDER BY U.PARAM_CODE ", condition);
            }

            try
            {
                lst = SqlHelper.ExecuteList<Param>(strSql, CommandType.Text);

                //总条数
                strSql = @"select count(*) as rowsCount
FROM SBRM_PARAM u where ACTIVE = 'Y' {0} ";
                strSql = string.Format(strSql, condition);
                object oCount = SqlHelper.ExecuteScalar(strSql, CommandType.Text);
                int total = Convert.ToInt32(oCount);
                return (new Result<Param> { rows = lst, total = total }).ToJson();
            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();
            }
        }

        public string addParam(HttpContext context)
        {
            string strSql = string.Empty;
            Param ParamAdd = JsonHelper.FromJson<Param>(context.Request.Form["data"]);
            ParamAdd.ACTIVE = "Y";
            //判断是否存在
            object result;
            strSql = "select count(*) from SBRM_PARAM where PARAM_CODE = '{0}' and PARAM_TYPE = '{1}' and ACTIVE = 'Y' ";
            strSql = string.Format(strSql, ParamAdd.PARAM_CODE, ParamAdd.PARAM_TYPE);
            result = SqlHelper.ExecuteScalar(strSql, CommandType.Text);

            if (result != null && result != DBNull.Value)
            {
                if (Convert.ToInt32(result) > 0) throw new Exception("此参数代码已经存在！");
            }

            strSql = "select count(*) from SBRM_PARAM where PARAM_CODE = '{0}' and PARAM_TYPE = '{1}' and ACTIVE = 'N' ";
            strSql = string.Format(strSql, ParamAdd.PARAM_CODE, ParamAdd.PARAM_TYPE);
            result = SqlHelper.ExecuteScalar(strSql, CommandType.Text);

            if (result != null && result != DBNull.Value)
            {
                if (Convert.ToInt32(result) > 0) throw new Exception("此参数已被删除，是否还原？");
            }

            //新增
            bool result2 = true;
            User user = context.Session["user"] as User;
            strSql = @"insert into SBRM_PARAM
(PARAM_TYPE,PARAM_CODE,PARAM_NAME,MEMO,ACTIVE,
UPDATE_USER_KEY,UPDATE_USER_ID,UPDATE_USER_NAME,UPDATE_DATE) 
values 
(@PARAM_TYPE,@PARAM_CODE,@PARAM_NAME,@MEMO,@ACTIVE,
@UPDATE_USER_KEY,@UPDATE_USER_ID,@UPDATE_USER_NAME, GETDATE()) ";

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //插入
                    cmd.CommandText = strSql;
                    var parms = new SqlParameter[] {
                        new SqlParameter{ ParameterName="@PARAM_TYPE", Value= ParamAdd.PARAM_TYPE },
                        new SqlParameter{ ParameterName="@PARAM_CODE", Value=ParamAdd.PARAM_CODE },
                        new SqlParameter{ ParameterName="@PARAM_NAME", Value= ParamAdd.PARAM_NAME },
                        new SqlParameter{ ParameterName="@MEMO", Value=ParamAdd.MEMO },
                        new SqlParameter{ ParameterName="@ACTIVE", Value=ParamAdd.ACTIVE },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY", Value=user.USER_KEY },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID", Value=user.USER_ID  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME", Value=user.USER_NAME }
                    };
                    cmd.Parameters.AddRange(parms);
                    cmd.ExecuteNonQuery();                    
                    //插入日志
                    insertLog(user, ParamAdd, "ADD", tx);
                    tx.Commit();
                }
                catch (Exception E)
                {
                    result2 = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            if (result2) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        public string editParam(HttpContext context)
        {
            string strSql = string.Empty;
            Param ParamEdit = JsonHelper.FromJson<Param>(context.Request.Form["data"]);
            ParamEdit.ACTIVE = "Y";
            string attachWhere = string.Empty;

            //修改
            bool result2 = true;
            User user = context.Session["user"] as User;
            strSql = @"update SBRM_PARAM
set MEMO=@MEMO,
UPDATE_USER_KEY=@UPDATE_USER_KEY,UPDATE_USER_ID=@UPDATE_USER_ID,UPDATE_USER_NAME=@UPDATE_USER_NAME,UPDATE_DATE=GETDATE()
where PARAM_TYPE = @PARAM_TYPE and PARAM_CODE=@PARAM_CODE ";
            strSql = string.Format(strSql, Common.Common.ReturnIdentitySql);

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //插入
                    cmd.CommandText = strSql;
                    var parms = new SqlParameter[] {
                        new SqlParameter{ ParameterName="@MEMO", Value=ParamEdit.MEMO },
                        new SqlParameter{ ParameterName="@PARAM_TYPE", Value= ParamEdit.PARAM_TYPE },
                        new SqlParameter{ ParameterName="@PARAM_CODE", Value=ParamEdit.PARAM_CODE },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY", Value=user.USER_KEY },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID", Value=user.USER_ID  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME", Value=user.USER_NAME }
                    };
                    cmd.Parameters.AddRange(parms);
                    cmd.ExecuteNonQuery();
                    //插入日志
                    insertLog(user, ParamEdit, "EDIT", tx);
                    tx.Commit();
                }
                catch (Exception E)
                {
                    result2 = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }

            if (result2) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        public string deleteParam(HttpContext context)
        {
            string strSql = string.Empty;
            Param ParamEdit = JsonHelper.FromJson<Param>(context.Request.Form["data"]);
            ParamEdit.ACTIVE = "N";
            string attachWhere = string.Empty;

            //删除
            bool result2 = true;
            User user = context.Session["user"] as User;
            strSql = @"update SBRM_PARAM
set ACTIVE=@ACTIVE,
UPDATE_USER_KEY=@UPDATE_USER_KEY,UPDATE_USER_ID=@UPDATE_USER_ID,UPDATE_USER_NAME=@UPDATE_USER_NAME,UPDATE_DATE=GETDATE()
where PARAM_TYPE = @PARAM_TYPE and PARAM_CODE = @PARAM_CODE ";
            strSql = string.Format(strSql, Common.Common.ReturnIdentitySql);

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //插入
                    cmd.CommandText = strSql;
                    var parms = new SqlParameter[] {
                        new SqlParameter{ ParameterName="@ACTIVE", Value=ParamEdit.ACTIVE },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY", Value=user.USER_KEY },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID", Value=user.USER_ID  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME", Value=user.USER_NAME },
                        new SqlParameter{ ParameterName="@PARAM_TYPE", Value=ParamEdit.PARAM_TYPE },
                        new SqlParameter{ ParameterName="@PARAM_CODE", Value=ParamEdit.PARAM_CODE }
                    };
                    cmd.Parameters.AddRange(parms);
                    cmd.ExecuteNonQuery();
                    //插入日志
                    insertLog(user, ParamEdit, "DELETE", tx);
                    tx.Commit();
                }
                catch (Exception E)
                {
                    result2 = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }

            if (result2) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        public string revertParam(HttpContext context)
        {
            Param ParamRevert = JsonHelper.FromJson<Param>(context.Request.Form["data"]);
            ParamRevert.ACTIVE = "Y";

            //还原已经删除的数据
            bool result2 = true;
            User user = context.Session["user"] as User;
            string strSql = @"update SBRM_PARAM
set PARAM_NAME=@PARAM_NAME,MEMO=@MEMO,ACTIVE=@ACTIVE,
UPDATE_USER_KEY=@UPDATE_USER_KEY,UPDATE_USER_ID=@UPDATE_USER_ID,UPDATE_USER_NAME=@UPDATE_USER_NAME,UPDATE_DATE=GETDATE()
where PARAM_TYPE = @PARAM_TYPE and PARAM_CODE = @PARAM_CODE ";
            strSql = string.Format(strSql, Common.Common.ReturnIdentitySql);

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //插入
                    cmd.CommandText = strSql;
                    var parms = new SqlParameter[] {
                        new SqlParameter{ ParameterName="@PARAM_NAME", Value= ParamRevert.PARAM_NAME },
                        new SqlParameter{ ParameterName="@MEMO", Value=ParamRevert.MEMO },
                        new SqlParameter{ ParameterName="@ACTIVE", Value=ParamRevert.ACTIVE },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY", Value=user.USER_KEY },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID", Value=user.USER_ID  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME", Value=user.USER_NAME },
                        new SqlParameter{ ParameterName="@PARAM_TYPE", Value=ParamRevert.PARAM_TYPE },
                        new SqlParameter{ ParameterName="@PARAM_CODE", Value=ParamRevert.PARAM_CODE }
                    };
                    cmd.Parameters.AddRange(parms);
                    cmd.ExecuteNonQuery();
                    //插入日志
                    insertLog(user, ParamRevert, "REVERT", tx);
                    tx.Commit();
                }
                catch (Exception E)
                {
                    result2 = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }

            if (result2) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        private void insertLog(User user, Param Param, string OperateStatus, SqlTransaction tx)
        {
            string strSql = @"insert into SBRM_PARAM_LOG
(PARAM_TYPE,PARAM_CODE,PARAM_NAME,MEMO,ACTIVE,
UPDATE_USER_KEY,UPDATE_USER_ID,UPDATE_USER_NAME,UPDATE_DATE, STATUS) 
values 
(@PARAM_TYPE,@PARAM_CODE,@PARAM_NAME,@MEMO,@ACTIVE,
@UPDATE_USER_KEY,@UPDATE_USER_ID,@UPDATE_USER_NAME, GETDATE(),@STATUS) ";

            var parms = new SqlParameter[] {
                    new SqlParameter{ ParameterName="@PARAM_TYPE", Value=Param.PARAM_TYPE },
                    new SqlParameter{ ParameterName="@PARAM_CODE", Value=Param.PARAM_CODE },
                    new SqlParameter{ ParameterName="@PARAM_NAME", Value= Param.PARAM_NAME },
                    new SqlParameter{ ParameterName="@MEMO", Value= Param.MEMO },
                    new SqlParameter{ ParameterName="@ACTIVE", Value=Param.ACTIVE },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY", Value=user.USER_KEY },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID", Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME", Value=user.USER_NAME },
                    new SqlParameter{ ParameterName="@STATUS", Value=OperateStatus }
            };

            if (tx != null)
                SqlHelper.ExecuteNonQuery(tx, strSql, CommandType.Text, parms);
            else
                SqlHelper.ExecuteNonQuery(strSql, CommandType.Text, parms);
        }

        public string getParamType(HttpContext context)
        {
            string strSql = @"SELECT u.* FROM SBRM_PARAM_TYPE u order by PARAM_TYPE ";

            try
            {
                List<ParamType> lst = SqlHelper.ExecuteList<ParamType>(strSql, CommandType.Text);
                return lst.ToJson();

                //总条数
                //strSql = @"select count(*) as rowsCount FROM SBRM_PARAM_TYPE u ";
                //object oCount = SqlHelper.ExecuteScalar(strSql, CommandType.Text);
                //int total = Convert.ToInt32(oCount);
                //return (new Result<ParamType> { rows = lst, total = total }).ToJson();
            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();
            }
        }
    }
}
